

package com.ehualu.getes.until;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class OracleUtills {


    /*
     * 得的对应位置卡口
     * */
    public List<String> getkkidlike(String ykkbh, String ykkmc, String ykkjd, String ykkwd) {

        Connection conn = DBConnection.getcon();

        List<String> ppkklist = new ArrayList<String>();

        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

//            String sqldy = String.format("select KKBH from kkxx t where ((t.kkjd = '%s' and t.kkwd = '%s') or t.kkmc like '%%%s%%') and t.kkbh <> '%s'", ykkjd, ykkwd, ykkmc, ykkbh);
            String sqldy = String.format("select KKBH from kkxx t where ((t.kkjd = '%s' and t.kkwd = '%s') or t.kkmc like '%s%%') and t.kkbh <> '%s' ", ykkjd, ykkwd, ykkmc, ykkbh);

            System.out.println("***********sql*******:" + sqldy);

            rs = st.executeQuery(sqldy);


            while (rs.next()) {

                String kkbh = rs.getString("KKBH");

                System.out.println("kkbh：" + kkbh);
                ppkklist.add(kkbh);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }

        return ppkklist;

    }


    public void getkkttt(String ykkmc) {


        Connection conn = DBConnection.getcon();

        List<String> ppkklist = new ArrayList<String>();

        Map<String, Double> info = new HashMap<String, Double>();


//        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            // Execute Query
//            String sql = "select * from v_kkxx where KKDM in ('%s','%s')"
            String sqldy = null;
//            select * from v_kkxx where (KKMC  like '%东昌路%' or KKMC like '%柳园路%' )and KKDM in ('3715000620','3715000621')
//            sql = String.format("select JD,WD from SKKXX where KKDM in ('%s','%s')", kkdm1, kkdm2);
            sqldy = String.format("select * from kkxx t where t.kkmc like '%%%s%%'", ykkmc);
            System.out.println("***********sql*******:" + sqldy);


            rs = st.executeQuery(sqldy);

            int sqldyct = rs.getRow();
            rs.getRow();


            System.out.println("******sqldyct******:" + sqldyct);

            while (rs.next()) {

                String kkbh = rs.getString("KKBH");

                System.out.println("******kkbh******:" + kkbh);


                ppkklist.add(kkbh);

            }


//            int inum = 1;
//            while (rs.next()) {
//
//                Double JD = rs.getDouble("JD");
//                Double WD = rs.getDouble("WD");
//                info.put("JD" + inum, JD);
//                info.put("WD" + inum, WD);
//                inum = inum + 1;
//            }
//
//            Double jd1 = info.get("JD1");
//
//            Double wd1 = info.get("WD1");
//            Double jd2 = info.get("JD2");
//
//            Double wd2 = info.get("WD2");
//
//            System.out.println("*****jd1******:" + jd1);
//            System.out.println("*****wd1******:" + wd1);
//            System.out.println("*****jd2******:" + jd2);
//            System.out.println("*****wd2******:" + wd2);
//
//            if (jd1 == 0 || wd1 == 0 || jd2 == 0 || wd2 == 0) {
//
//                dislong = 0;
//
//            } else {
//
//                dislong = DistanceUtils.getDistance(jd1, wd1, jd2, wd2);
//            }
//
//
//            System.out.println("*****dislong******:" + dislong);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }


    }


    public String getkkctfordl(String dldm) {


        Connection conn = DBConnection.getcon();

        List<String> ppkklist = new ArrayList<String>();

        Map<String, Double> info = new HashMap<String, Double>();

        String kkctnum = "";
//        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            // Execute Query
//            String sql = "select * from v_kkxx where KKDM in ('%s','%s')"
            String sqldy = null;
//            select * from v_kkxx where (KKMC  like '%东昌路%' or KKMC like '%柳园路%' )and KKDM in ('3715000620','3715000621')
//            sql = String.format("select JD,WD from SKKXX where KKDM in ('%s','%s')", kkdm1, kkdm2);
            sqldy = String.format("select count(*) as CT from kkxx t where t.dldm ='%s'", dldm);
            System.out.println("***********sql*******:" + sqldy);


            rs = st.executeQuery(sqldy);

            while (rs.next()) {

                kkctnum = rs.getString("CT");


            }


//            return kkctnum;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }

        return kkctnum;

    }


    public List<String> getDldm(String dllx, String dlmcbh) {

        Connection conn = DBConnection.getcon();

        List<String> dldmlist = new ArrayList<>();

        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            String sqldy = null;


            if (dllx.equals("G")) {

                sqldy = String.format("select DLDM, DLMC\n" +
                        "  from DLDMBATAB t\n" +
                        " where (t.dlmc like '%%G%%' or t.dlmc like '%%g%%' or t.dlmc like '%%国道%%')\n" +
                        "   and t.dlmc like '%%%s%%'", dlmcbh);
                System.out.println("***********sql*******:" + sqldy);

            } else if (dllx.equals("S")) {

                sqldy = String.format("select DLDM, DLMC\n" +
                        "  from DLDMBATAB t\n" +
                        " where (t.dlmc like '%%S%%' or t.dlmc like '%%s%%' or t.dlmc like '%%省道%%')\n" +
                        "   and t.dlmc like '%%%s%%'\n", dlmcbh);
                System.out.println("***********sql*******:" + sqldy);

            } else {

                return dldmlist;
            }

            System.out.println("***********sql*******:" + sqldy);

            rs = st.executeQuery(sqldy);

            while (rs.next()) {

                String strdldm = rs.getString("DLDM");

                System.out.println("****strdldm****" + strdldm);

                dldmlist.add(strdldm);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }
        }

        return dldmlist;
    }


    /*
     * 得到危化品车牌(总)
     * */
    public List<String> getwhpcp() {

        Connection conn = DBConnection.getcon();

        List<String> whpcplist = new ArrayList<>();

        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            String sqldy = "select distinct(v.hphm)  from TRFF_APP.vehicle v where v.syxz ='R' and zt not like '%B%' and zt not like '%E%'";


            System.out.println("***********sql*******:" + sqldy);

            rs = st.executeQuery(sqldy);

            while (rs.next()) {

                String strhphm = "鲁" + rs.getString("HPHM");

//                System.out.println("****strdldm****" + strhphm);

                whpcplist.add(strhphm);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }

        }

        return whpcplist;
    }


    /*
     * 得到危化品车牌(普通)
     * */
    public List<String> getwhpcppt() {

        Connection conn = DBConnection.getcon();

        List<String> whpcplist = new ArrayList<>();

        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            String sqldy = "select distinct(v.hphm)  from TRFF_APP.vehicle v where v.syxz ='R' and zt not like '%B%' and zt not like '%E%'  and v.hpzl ='02'";


            System.out.println("***********sql*******:" + sqldy);

            rs = st.executeQuery(sqldy);

            while (rs.next()) {

                String strhphm = "鲁" + rs.getString("HPHM");

//                System.out.println("****strdldm****" + strhphm);

                whpcplist.add(strhphm);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }

        }

        return whpcplist;
    }

    /*
     * 得到危化品车牌(货车)
     * */
    public List<String> getwhpcphc() {

        Connection conn = DBConnection.getcon();

        List<String> whpcplist = new ArrayList<>();

        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            String sqldy = "select distinct(v.hphm)  from TRFF_APP.vehicle v where v.syxz ='R' and zt not like '%B%' and zt not like '%E%' and v.hpzl <>'02'";


            System.out.println("***********sql*******:" + sqldy);

            rs = st.executeQuery(sqldy);

            while (rs.next()) {

                String strhphm = "鲁" + rs.getString("HPHM");

//                System.out.println("****strdldm****" + strhphm);

                whpcplist.add(strhphm);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }

        }

        return whpcplist;
    }






/*
    public static void main(String[] args) {

        Connection conn = DBConnection.getcon();


//        Connection conn = DBConnection.getehldb();
        OracleUtills oracleUtills = new OracleUtills();


        List<String> whpcplist = oracleUtills.getkkidlike("3715010112415","东昌路与黄山路","116.041733","36.464115");

        System.out.println("****whpcplist size****" + whpcplist.size());


//        oracleUtills.getkkttt("黑龙江蒋广路");
//        double ls = OracleUtills.dissize("3715000761", "3715000575");


//        double ls = OracleUtills.realdislongm("3715000559", "3715000829");
//        double ls = OracleUtills.dissizet("3715000518", "3715000520");
//        for (int i = 1; i < 5; i++) {

//            OracleUtills.writeora("3715000653_3715000513" + i, "0.25477513389667844", "拥堵");
//        }

//
        if (conn != null) {

            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }


    }
*/
}
